# -*- coding: utf-8 -*-
from WindPy import *
import numpy as np
import pandas as pd
from datetime import *
import matplotlib.pyplot as plt
import os.path
import xlwt
import xlrd
#from sqlalchemy import create_engine,text
#from sqlalchemy.orm import sessionmaker
os.chdir("F:\\PA80") 

#计算涨跌幅序列，夏普比率，最大回撤，波动率
def net_index(list1):
    drawdown=0
    for i in range(len(list1)):
        a=0
        for j in range(0,i+1):
            if a<list1[j]:
                a=list1[j]
        if drawdown<1-list1[j]/a:
            drawdown=1-list1[j]/a
    yie=[]
    for i in range(len(list1)-1):
        yie.append(np.log(list1[i+1]/list1[i]))
    sharp_ratio=(np.sum(yie)/len(yie)*250-0.03)/((np.std(pd.Series(yie))*np.sqrt(250)))
    std=np.std(pd.Series(yie))*np.sqrt(250)
    return drawdown,sharp_ratio,std
    
#对字典排序，按值从大到小排序，d[1]为按照value排序    
def rank_reverse(dict1):
    list1=sorted(dict1.items(),key=lambda d:d[1],reverse=True)
    dict2={}
    for i in range(len(list1)):
        dict2[list1[i][0]]=i+1
    return dict2
    
#对字典排序，按值从小到大排序，默认
def rank_order(dict1):
    list1=sorted(dict1.items(),key=lambda d:d[1],reverse=False)
    dict2={}
    for i in range(len(list1)):
        dict2[list1[i][0]]=i+1
    return dict2

w.start()
start='20090101'
stop='20171110'

#调仓频率
frequency=20
#区间所有交易日
trade=w.tdays(start,stop).Data[0]

#调仓日期
i=0
change=[]
for day in trade:
    if (i%frequency==0):
        change.append(trade[i])
    i+=1

#选股回测

code_list_set=[]
r1=0.3; r2=0.2; r3=0.25; r4=0.15; r5=0.1      # 因子权重
top=80; 
select=[];   
for date in change:
#    i= change.index(date)
    print ('selecting ',date.strftime("%Y-%m-%d"))
    code_list=w.wset("sectorconstituent","date="+date.strftime("%Y-%m-%d")+";sectorid=a001010100000000;field=wind_code").Data[0]
    west_eps=w.wsd(code_list, "west_eps_FTM", date, date, "Fill=Previous;PriceAdj=F").Data[0]  #一致预测eps 未来12个月
    eps_now=w.wsd(code_list, "eps_ttm", date, date, "Fill=Previous;PriceAdj=F").Data[0]  #当前eps 
    west_pe=w.wsd(code_list, "pe_est_ftm", date, date, "Fill=Previous;PriceAdj=F").Data[0]  #预测pe, 未来12个月
    roe=w.wsd(code_list, "roe_ttm",date, date, "Fill=Previous;PriceAdj=F").Data[0]  #历史roe  净资产收益率
    profit=w.wsd(code_list, "op_ttm", date, date, "unit=1;Fill=Previous;PriceAdj=F").Data[0]  #营业利润
    revenue=w.wsd(code_list, "or_ttm", date, date, "unit=1;Fill=Previous;PriceAdj=F").Data[0]  #营业收入
    brate=w.wsd(code_list, "div_aualaccmdivpershare", date, date, 'year='+str(date.year)+';Fill=Previous;PriceAdj=F').Data[0]  #年度累计单位分红
    trade_status=w.wsd(code_list, "trade_status", date, date).Data[0]     # 读取交易状态    
    f1={}; f2={}; f3={}; f4={}; f5={}; trade_s={}
    for code in code_list:
        j = code_list.index(code)
        if trade_status[j] is None:
            trade_s[code]=10000
        elif trade_status[j].encode('utf-8')==u'交易'.encode('utf-8'):
            trade_s[code]=0     #0为正常交易
        else:
            trade_s[code]=10000
        if west_eps[j] is None or west_eps[j]!=west_eps[j] or type(west_eps[j])==type('123') or eps_now[j]!=eps_now[j] or type(eps_now[j])==type('123') or eps_now[j] is None or eps_now[j]<0.00000001:
            f1[code]=-10000.0
        else:
            f1[code]=west_eps[j]/eps_now[j]-1   #EPSGrw因子，越大收益越大
        if roe[j] is None or roe[j]!=roe[j] or type(roe[j])==type('123'):
            f2[code] = -10000.0
        else:
            f2[code] = roe[j]     #roe， 越大收益越大
        if west_pe[j] is None or west_pe[j]!=west_pe[j] or type(west_pe[j])==type('123') or west_eps[j]<0.000000001:
            f3[code] = -10000.0   
        else:
            f3[code] = 1/west_pe[j]     #forword ep 越大收益越大
        if profit[j] is None or profit[j]!=profit[j] or type(profit[j])==type('123') or revenue[j] is None or revenue[j]!=revenue[j] or type(revenue[j])==type('123') or revenue[j]<1.0:
            f4[code]=-10000.0
        else:
            f4[code]=profit[j]/revenue[j]   #neopr因子，越大收益越大
        if brate[j] is None or brate[j]!=brate[j] or type(brate[j])==type('123'):
            f5[code]=-10000.0
        else:
            f5[code]=brate[j]  #PR因子，越大收益越大
    rank1=rank_reverse(f1)
    rank2=rank_reverse(f2)
    rank3=rank_reverse(f3)
    rank4=rank_reverse(f4)
    rank5=rank_reverse(f5)
    rank_mix={}
    for code in code_list:
        rank_mix[code]=r1*rank1[code]+r2*rank2[code]+r3*rank3[code]+r4*rank4[code]+r5*rank5[code]+trade_s[code]
    final_result=sorted(rank_mix.items(),key=lambda d:d[1],reverse=False)
    selecti=[];
    for j in range(top):       
        selecti.append(final_result[j][0])
    select.append(selecti)
        
    for code in code_list:
        if code not in code_list_set:
            code_list_set.append(code)

original_data2=w.wsd(code_list_set,'close',start,stop,'Fill=Previous;PriceAdj=F')
price_info={} 
           
for code in code_list_set:
    h=code_list_set.index(code)
    price_info[code]=original_data2.Data[h]
    
cash=5000000.0
stock_value=[]; 
stock_inhold_all=[];  
stock_inhold={}; 
for today in trade:                
    i=trade.index(today)
    if i==0:
        stock_value.append(float(cash))
    else:
        value=0
        for key in stock_inhold:
            value+=stock_inhold[key]*price_info[key][i]
        stock_value.append(value)
    if today in change:
        k=change.index(today)
        per_value=stock_value[i]/float(top)
        stock_inhold={}
        if k==0:
            for j in range(top):
                stock_inhold[select[0][j]]=per_value/price_info[select[0][j]][i]
        else:
            for j in range(top):
                stock_inhold[select[k][j]]=per_value/price_info[select[k][j]][i]
    stock_inhold_all.append(stock_inhold)

index = '000300.SH'      
index_value=w.wsd(index,'close',start,stop).Data[0]
datelist = [dt.strftime('%Y-%m-%d') for dt in trade]
changelist = [dt.strftime('%Y-%m-%d') for dt in change]

def chg_drawdown(stock_value):
    drawdownlist=[]
    pct_chglist=[]
    for value in stock_value:
        i = stock_value.index(value)
        if i == 0:
            down=0
            chg_amt=0
        else:
            down = abs(value/np.max(pd.Series(stock_value[0:i]))-1)
            chg_amt=stock_value[i]/stock_value[i-1]-1
        drawdownlist.append(down)
        pct_chglist.append(chg_amt)
    return drawdownlist,pct_chglist
             
def generate_df(stock_value,index_value,datelist,index,top):
    drawdown,sharp_ratio,std=net_index(stock_value)
    drawdownlist,pct_chglist=chg_drawdown(stock_value)
    returnlist = list(pd.Series(stock_value)/stock_value[0]-1)
    index_return = list(pd.Series(index_value)/index_value[0]-1)
    dataDF=pd.DataFrame({'date':datelist,'return':returnlist,'drawdown':drawdownlist,'pct_chg':pct_chglist,'index_return':index_return,'top':top,'maxdrawdown':drawdown,'sharp_ratio':sharp_ratio,'stdlist':std,'stock_value':stock_value,'index_chg':index_chglist})
    return dataDF  

index_downlist,index_chglist=chg_drawdown(index_value)    
    
data1 = generate_df(stock_value,index_value,datelist,index,top)  #top80 结果
            
#回测结果的excel文件名          
alo1='PA80_'+str(top)+'_from_'+datelist[0]+'_to_'+datelist[-1]+'.csv'
data1.to_csv(alo1, encoding='gbk')

file1=xlwt.Workbook(encoding='utf-8',style_compression=0)
sheet=file1.add_sheet('stock_hold')


for item in changelist:
    m=datelist.index(item)
    if m==0:
        i=0
        sheet.write(i,0,item)
    else:
        i=i+1
        sheet.write(i,0,item)
    j=1
    for jtem in stock_inhold_all[m].keys():
        sheet.write(i,j,jtem)
        j=j+1
       
#持仓明细的excel文件名
file1.save('stockhold_PA80'+'_from_'+datelist[0]+'_to_'+datelist[-1]+str(top)+'.xls')


